#!/bin/bash
# 定义变量：数据同步日期，表示同步哪一条日志数据
# 第1、执行shell脚本时，传递参数
# 第2、如果没有传递参数，同步前一天数据
# todo 大数据-电商数仓-07-商品主题商品诊断看板
if [ -n "$1" ] ; then
data_date=$1
else
data_date=`date -d "-1 days" +%F`
fi
# 加载数据语句
DIM_MD_AREAS_SQL="
USE product_order;
WITH t1 AS
(
    SELECT * FROM product_order.dwd_product_base_info WHERE dt = '${data_date}'
),t2 AS
(
    SELECT * FROM product_order.dwd_product_trade_analysis WHERE dt = '${data_date}'
)
INSERT OVERWRITE TABLE product_order.dws_product_traffic_metrics PARTITION (dt = '${data_date}')
SELECT db.product_id,
       db.product_name,
       db.category_id,
       db.category_name,
       COUNT(DISTINCT IF( behavior_type = 'click' , user_id , NULL))   AS total_visits,
       COUNT(DISTINCT IF(channel = '手淘搜索' AND behavior_type = 'click' , user_id,NULL )) AS search_visits,
       COUNT(DISTINCT IF(channel = '聚划算' AND behavior_type = 'click' , user_id,NULL ))  AS jhs_visits,
       COUNT(DISTINCT IF(channel = '直通车' AND behavior_type = 'click' , user_id,NULL )) AS direct_visits
FROM t1 db
LEFT JOIN t2 dp ON db.product_id = dp.product_id
GROUP BY db.product_id, db.product_name,db.category_id,db.category_name;

WITH t1 AS
(
    SELECT * FROM product_order.dwd_product_base_info WHERE dt = '${data_date}'
),t2 AS
(
    SELECT * FROM product_order.dwd_product_trade_analysis WHERE dt = '${data_date}'
)
INSERT OVERWRITE TABLE product_order.dws_product_conversion_metrics PARTITION (dt = '${data_date}')
SELECT db.product_id,
       db.product_name,
       db.category_id,
       db.category_name,
       ROUND(COUNT(DISTINCT CASE WHEN dp.order_status = '已支付' THEN dp.user_id END) * 100.0
                 / NVL(COUNT(DISTINCT dp.user_id), 0),
             2) AS payment_rate,
       ROUND(COUNT(DISTINCT CASE WHEN dp.behavior_type = 'cart' THEN dp.user_id END) * 100.0
                 / NVL(COUNT(DISTINCT CASE WHEN dp.behavior_type = 'click' THEN dp.user_id END), 0),
             2) AS addcart_rate,
       ROUND(COUNT(DISTINCT CASE WHEN dp.behavior_type = 'favorite' THEN dp.user_id END) * 100.0
                 / NVL(COUNT(DISTINCT CASE WHEN dp.behavior_type = 'click' THEN dp.user_id END), 0),
             2)  AS collect_rate,
       ROUND(NVL(SUM(dp.payment_amount)
                     / NVL(COUNT(DISTINCT CASE WHEN dp.behavior_type = 'click' THEN dp.user_id END), 0), 0),
             2)  AS avg_customer_value
FROM t1 db
LEFT JOIN t2 dp ON db.product_id = dp.product_id
GROUP BY db.product_id, db.product_name,db.category_id,db.category_name;



WITH t1 AS
 (
     SELECT * FROM product_order.dwd_product_base_info WHERE dt = '${data_date}'
 ),t2 AS
 (
     SELECT * FROM product_order.dwd_product_trade_analysis WHERE dt = '${data_date}'
 )
INSERT OVERWRITE TABLE product_order.dws_product_content_metrics PARTITION (dt = '${data_date}')
SELECT db.product_id,
       db.product_name,
       db.category_id,
       db.category_name,
       COUNT(DISTINCT CASE WHEN channel != '其他' AND behavior_type = 'click' THEN user_id END) AS content_visits,
       ROUND( COUNT(DISTINCT CASE WHEN channel != '其他' AND behavior_type = 'favorite' THEN user_id END) * 100.0
                   / NVL(COUNT(DISTINCT CASE WHEN channel != '其他' AND behavior_type = 'click' THEN user_id END), 0)
           , 2)  AS content_collect_rate,
       ROUND( COUNT(DISTINCT CASE WHEN channel != '其他' AND behavior_type = 'cart' THEN user_id END) * 100.0
                   / NVL(COUNT(DISTINCT CASE WHEN channel != '其他' AND behavior_type = 'click' THEN user_id END), 0)
           , 2) AS content_addcart_rate,
       NVL(SUM(dp.payment_amount), 0) AS content_payment,
       COUNT(DISTINCT
             CASE
                 WHEN channel != '其他' AND dp.order_status = '已支付'
                     THEN dp.user_id
            END) AS content_buyers
FROM t1 db
LEFT JOIN t2 dp ON db.product_id = dp.product_id
WHERE db.dt = '${data_date}'
  AND dp.channel != '其他'
GROUP BY db.product_id, db.product_name,db.category_id,db.category_name;


WITH t1 AS
 (
     SELECT * FROM product_order.dwd_product_base_info WHERE dt = '${data_date}'
 ),t2 AS
 (
     SELECT * FROM product_order.dwd_product_trade_analysis WHERE dt = '${data_date}'
 )
INSERT OVERWRITE TABLE product_order.dws_product_newbuyer_metrics PARTITION (dt = '${data_date}')
SELECT db.product_id,
       db.product_name,
       db.category_id,
       db.category_name,
       ROUND(SUM(CASE WHEN dp.is_new_buyer = 1 THEN dp.payment_amount ELSE 0 END) * 100.0
                 / NVL(SUM(dp.payment_amount), 0), 2)     AS new_payment_ratio,
       ROUND(COUNT(DISTINCT CASE WHEN dp.is_new_buyer = 1 THEN dp.user_id END) * 100.0
                 / NVL(COUNT(DISTINCT dp.user_id), 0), 2) AS new_buyer_ratio
FROM t1 db
LEFT JOIN t2 dp ON db.product_id = dp.product_id
WHERE dp.dt = '${data_date}'
  AND dp.order_status = '已支付'
GROUP BY db.product_id, db.product_name,db.category_id,db.category_name;


WITH t1 AS
 (
     SELECT * FROM product_order.dwd_product_base_info WHERE dt = '${data_date}'
 ),t2 AS
 (
     SELECT * FROM product_order.dwd_product_trade_analysis WHERE dt = '${data_date}'
 )
INSERT OVERWRITE TABLE product_order.dws_product_service_metrics PARTITION (dt = '${data_date}')
SELECT db.product_id,
       db.product_name,
       db.category_id,
       db.category_name,
       COUNT(CASE WHEN images IS NOT NULL THEN review_id END) AS image_reviews,
       COUNT(CASE WHEN appraise = '1' THEN review_id END) AS positive_reviews,
       ROUND(COUNT(CASE WHEN dp.order_status = '已退款' THEN dp.order_id END) * 100.0
                 / NVL(COUNT(CASE WHEN dp.order_status = '已支付' THEN dp.order_id END), 0), 2) AS refund_rate
FROM t1 db
LEFT JOIN t2 dp ON db.product_id = dp.product_id
WHERE dp.dt = '${data_date}'
GROUP BY db.product_id, db.product_name,db.category_id,db.category_name;

INSERT OVERWRITE TABLE product_order.dws_product_wide_metrics PARTITION (dt = '${data_date}')
SELECT
    t.product_id,
    t.product_name,
    t.category_id,
    t.category_name,
    t.total_visits,
    t.search_visits,
    t.jhs_visits,
    t.direct_visits,
    c.payment_rate,
    c.addcart_rate,
    c.collect_rate,
    c.avg_customer_value,
    m.content_visits,
    m.content_collect_rate,
    m.content_addcart_rate,
    m.content_payment,
    m.content_buyers,
    n.new_payment_ratio,
    n.new_buyer_ratio,
    s.image_reviews,
    s.positive_reviews,
    s.refund_rate
FROM (
         SELECT * FROM product_order.dws_product_traffic_metrics WHERE dt = '${data_date}'
     ) t
         LEFT JOIN (
    SELECT * FROM product_order.dws_product_conversion_metrics WHERE dt = '${data_date}'
) c ON t.product_id = c.product_id
         LEFT JOIN (
    SELECT * FROM product_order.dws_product_content_metrics WHERE dt = '${data_date}'
) m ON t.product_id = m.product_id
         LEFT JOIN (
    SELECT * FROM product_order.dws_product_newbuyer_metrics WHERE dt = '${data_date}'
) n ON t.product_id = n.product_id
         LEFT JOIN (
    SELECT * FROM product_order.dws_product_service_metrics WHERE dt = '${data_date}'
) s ON t.product_id = s.product_id ;

"
# 执行SQL语句
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_MD_AREAS_SQL}"